/*
	EXEC sp_wip_info_as_of_date 'FG','0','0','0','06/11/2007','0','all',''
	EXEC sp_wip_info_as_of_date '0','0','0','0','11/16/2007','0','all',''
*/

ALTER PROC sp_wip_info_as_of_date

@PROCESS_TYPE		VARCHAR(50),
@PROCESSSTEP_IDS 	VARCHAR(200),
@VENDOR			VARCHAR(50),	
@LOT_CATEGORY 		VARCHAR(20),
@ASOFDATE 		VARCHAR(50),
@STATUS 		VARCHAR(20),
@REPORT_TYPE 		VARCHAR(20),
@SEARCH_PART_NO 	VARCHAR(50)

AS
BEGIN

--Final Result table to store all the running lots Chiptrac based on the Search criteria
CREATE TABLE #WIP_RESULTS ( PROCESSSTEP_ID INT, LOCATION VARCHAR(50), LOT_NUMBER VARCHAR(50), FAB_LOT_NUMBER VARCHAR(50),
			LOT_CATEGORY VARCHAR(50), WAFER_QTY_IN INT, QTY_IN INT,	DATE_IN DATETIME, STAGE_NAME VARCHAR(50), 
			STAGE_POSITION VARCHAR(50), LOT_CURRENT_STATUS VARCHAR(50), EXPECTED_DATE_OUT DATETIME,
			OUTLOOK_DATE DATETIME, GROSS_DIE INT,WAFER_ID VARCHAR(200), 
			PART_NUMBER VARCHAR(50),BASE_PRODUCT VARCHAR(50), BASE_PRODUCT_ID INT,
			PS_TYPE_NAME VARCHAR(50),PS_TYPE_ID INT,THINKNESS VARCHAR(50),GRIND VARCHAR(50),
			SL_FLOW VARCHAR(50), SL_TEST_PROGRAM VARCHAR(50),SL_HOLD_DATE DATETIME,SL_TESTER VARCHAR(50),
			MASK VARCHAR(50),LABLE_NAME VARCHAR(50),PACKAGE_TYPE VARCHAR(50)
		 )

CREATE TABLE #FG_RESULTS ( PROCESSSTEP_ID INT, FG_VENDOR VARCHAR(50), LOT_NUMBER VARCHAR(50), FAB_LOT_NUMBER VARCHAR(50),
			LOT_CATEGORY VARCHAR(50), QTY_IN INT,DATE_IN DATETIME,LOT_CURRENT_STATUS VARCHAR(50),
			LOT_DATE_CODE VARCHAR(50),EXPIRY_DATE INT,lot_AGE INT,EXPECTED_DATE_OUT DATETIME, 
			PART_NUMBER VARCHAR(50),PIN_COUNT VARCHAR(20),PART_STATUS VARCHAR(20),PACKAGE_TYPE VARCHAR(50),
			ASSEMBLY_VENDOR VARCHAR(200),ASSEMBLY_PROCESSSTEP_ID INT)

--Intermediate tables
CREATE TABLE #STEP_IDS_TABLE  ( STEP_ID INT )
CREATE TABLE #ASSEMBLY_PROCESSSTEP_TABLE  ( FGPROCESSSTEP_ID INT, ASSEMBLY_PROCESSSTEP_ID INT )

DECLARE @TEMP_VARCHAR VARCHAR(50)
DECLARE @START_DATE DATETIME
DECLARE @END_DATE DATETIME

DECLARE @TRANSIT_STRING		VARCHAR(4000)
DECLARE @FG_REPORT 		VARCHAR(3000)
declare @FGTRANSIT_STRING	varchar(3000)

SET @TEMP_VARCHAR = CONVERT(VARCHAR, @ASOFDATE, 101)+' 12:00AM'
SET @START_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)
SET @TEMP_VARCHAR = NULL
SET @TEMP_VARCHAR = CONVERT(VARCHAR, @ASOFDATE, 101)+' 11:59PM'
SET @END_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)
/* 
	In this table when no Process Type is selected we will load BaseProduct Ids and 
	if Process Type is selected we we will loaad corresponding process Processstep_ids
*/
IF @PROCESSSTEP_IDS <> '0'
BEGIN
	IF @REPORT_TYPE = 'ALL' OR @REPORT_TYPE = 'WIP' OR @REPORT_TYPE = 'INVENTORY'
	BEGIN
		IF ( @PROCESS_TYPE = '0' OR @PROCESS_TYPE = 'WIP' OR @PROCESS_TYPE = 'INVENTORY' )
		BEGIN
			INSERT INTO #STEP_IDS_TABLE SELECT PROCESSSTEP_ID FROM PRODUCT WHERE P_PARENT_PRODUCT_ID IN ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ( @PROCESSSTEP_IDS ) ) 
		END
		ELSE
		BEGIN
			INSERT INTO #STEP_IDS_TABLE SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ( @PROCESSSTEP_IDS ) 
		END
	END
END

IF @PROCESS_TYPE = 'TAPE'
BEGIN
	SET @PROCESS_TYPE = 'TAPE & REEL'
END

IF @PROCESS_TYPE = 'Class Store'
BEGIN
	SET @PROCESS_TYPE = 'Pre Test'
END

	--IF ( (@REPORT_TYPE = 'finished' or @REPORT_TYPE = 'inventory' or @REPORT_TYPE = 'all') and @PROCESS_TYPE = 'FG' )
	IF ( @PROCESS_TYPE = '0' OR @PROCESS_TYPE = 'FT' OR @PROCESS_TYPE = 'Pre Test' OR @PROCESS_TYPE = 'EOL' OR @PROCESS_TYPE = 'TAPE & REEL' OR @PROCESS_TYPE = 'FG' )
	BEGIN
	
		DECLARE @FG_PROCESSSTEP_ID 	INT
		DECLARE @ASSM_PROCESSSTEP_ID 	INT
		DECLARE @PROCESSSTEP_STRING 	VARCHAR(1000)
						
		SET @PROCESSSTEP_STRING = N'
			DECLARE PROCESSSTEP_CURSOR CURSOR FOR 
			SELECT 
				DISTINCT LT.PROCESSSTEP_ID 
			FROM 
				LOT_TABLE_HISTORY LT,PRODUCT P,PROCESS_STEP PS 
			WHERE 
				LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID 
				AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID 
				AND PS_TYPE = ''FG'' '
	
			IF @PROCESSSTEP_IDS <> '0'
			BEGIN
				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND LT.PROCESSSTEP_ID IN ( SELECT STEP_ID FROM #STEP_IDS_TABLE ) '
			END
	
			IF @VENDOR <> '0'
			BEGIN
				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND LT.LOCATION_ID  IN ( SELECT LOCATION_ID FROM LOCATION WHERE L_VENDOR_NAME = '''+ @VENDOR +''''+')'
			END
	
			IF @LOT_CATEGORY <> '0'
			BEGIN
				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND LT.LOT_CATEGORY = '''+ @LOT_CATEGORY+''''
			END
	
			IF @STATUS = '0'
			BEGIN
				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND ( LOT_CURRENT_STATUS = ''RUNNING'' OR LOT_CURRENT_STATUS = ''HOLD'' ) '
			END
			ELSE
			BEGIN
				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND LOT_CURRENT_STATUS = '''+ @STATUS+''''
			END
	
			IF @SEARCH_PART_NO <> '' and LEN(LTRIM(RTRIM(@SEARCH_PART_NO))) > 0
			BEGIN
				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND  PART_NUMBER like  ''' + '%' + @search_Part_no + '%' + ''''
			END
	
			EXEC (@PROCESSSTEP_STRING)
	
			OPEN PROCESSSTEP_CURSOR 
			FETCH NEXT FROM PROCESSSTEP_CURSOR
				INTO @FG_PROCESSSTEP_ID
			WHILE @@FETCH_STATUS = 0
			BEGIN
				SELECT @ASSM_PROCESSSTEP_ID = PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOW_BY_PROCESSSTEP_ID ( @FG_PROCESSSTEP_ID ) WHERE PS_TYPE = 'ASSEMBLY'
				
				INSERT INTO #ASSEMBLY_PROCESSSTEP_TABLE (FGPROCESSSTEP_ID,ASSEMBLY_PROCESSSTEP_ID) VALUES (@FG_PROCESSSTEP_ID,@ASSM_PROCESSSTEP_ID)
			
					SET @FG_PROCESSSTEP_ID = NULL
					SET @ASSM_PROCESSSTEP_ID = NULL
			
				FETCH NEXT FROM PROCESSSTEP_CURSOR INTO @FG_PROCESSSTEP_ID
			END
				 																
			CLOSE PROCESSSTEP_CURSOR
			DEALLOCATE PROCESSSTEP_CURSOR 
		
		--SELECT * FROM #ASSEMBLY_PROCESSSTEP_TABLE
	END

	IF ( (@REPORT_TYPE = 'finished' or @REPORT_TYPE = 'inventory' or @REPORT_TYPE = 'all') and @PROCESS_TYPE = 'FG' )
	BEGIN
		SET @FG_REPORT = N'INSERT INTO #FG_RESULTS
		SELECT 
			LT.PROCESSSTEP_ID, L_VENDOR_NAME AS FG_VENDOR,
			LOT_NUMBER,FAB_LOT_NUMBER,LOT_CATEGORY,QTY_IN,DATE_IN,
			(CASE
				WHEN LOT_CURRENT_STATUS = ''RUNNING'' THEN ''ACTIVE''
				ELSE ''HOLD''
			END) AS LOT_CURRENT_STATUS,
			LOT_DATE_CODE,DATEDIFF( DD,DATE_IN,ISNULL( IL.EXPECTED_DATE_OUT,CURRENT_TIMESTAMP ) )AS EXPIRY_DATE,DATEDIFF( DD,DATE_IN,'''+ CONVERT(VARCHAR,@ASOFDATE)+''' ) AS lot_AGE,
			IL.EXPECTED_DATE_OUT,PART_NUMBER,
			(SELECT PARAMETER_VALUE	FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ''PIN'' AND PROCESSSTEP_ID = PS.PROCESSSTEP_ID ) AS PIN_COUNT,
			 P.PART_STATUS,
			(SELECT PARAMETER_VALUE	FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ''PACKAGE ID'' AND PROCESSSTEP_ID = APT.ASSEMBLY_PROCESSSTEP_ID ) AS PACKAGE_TYPE,
			--(SELECT L_VENDOR_NAME FROM LOT_TABLE LT1,LOCATION L1 WHERE LT1.LOCATION_ID = L1.LOCATION_ID AND LT1.PROCESSSTEP_ID = APT.ASSEMBLY_PROCESSSTEP_ID AND LT1.FAB_LOT_NUMBER = LT.FAB_LOT_NUMBER ) --AND FGPROCESSSTEP_ID = LT.PROCESSSTEP_ID) 
			null AS ASSEMBLY_VENDOR,APT.ASSEMBLY_PROCESSSTEP_ID
		FROM 
			LOT_TABLE_HISTORY LT,INVENTORY_LOT IL, PRODUCT P, PROCESS_STEP PS,#ASSEMBLY_PROCESSSTEP_TABLE APT, Location L
		WHERE 
			LT.LOT_ID = IL.LOT_ID
			AND LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
			AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID 
			AND LT.LOCATION_ID = L.LOCATION_ID
			AND LT.TimeStamp = ( SELECT MAX(LTH.Timestamp) FROM LOT_TABLE_HISTORY LTH WHERE LTH.TIMESTAMP BETWEEN '''+ CONVERT(VARCHAR,@START_DATE)+''''+' AND '''+ CONVERT(VARCHAR,@END_DATE)+'''
			AND LTH.Lot_ID = Lt.Lot_ID )
			AND PS.PROCESSSTEP_ID = APT.FGPROCESSSTEP_ID
			AND PS_TYPE = ''FG'' '

		--SET @FG_REPORT = @FG_REPORT + ' AND LT.TIMESTAMP BETWEEN '''+ CONVERT(VARCHAR,@START_DATE)+''''+' AND '''+ CONVERT(VARCHAR,@END_DATE)+''''  
	
		IF @PROCESSSTEP_IDS <> '0'
		BEGIN
			SET @FG_REPORT = @FG_REPORT + ' AND LT.PROCESSSTEP_ID IN ( SELECT STEP_ID FROM #STEP_IDS_TABLE ) '
		END
		IF @VENDOR <> '0'
		BEGIN
			SET @FG_REPORT = @FG_REPORT + ' AND L_VENDOR_NAME = '''+ @VENDOR +''''
		END
		IF @LOT_CATEGORY <> '0'
		BEGIN
			SET @FG_REPORT = @FG_REPORT + ' AND LOT_CATEGORY = '''+ @LOT_CATEGORY+''''
		END
		IF @STATUS = '0'
		BEGIN
			SET @FG_REPORT = @FG_REPORT + ' AND ( LOT_CURRENT_STATUS = ''RUNNING'' OR LOT_CURRENT_STATUS = ''HOLD'' ) '
		END
		ELSE
		BEGIN
			SET @FG_REPORT = @FG_REPORT + ' AND LOT_CURRENT_STATUS = '''+ @STATUS+''''
		END
		IF @SEARCH_PART_NO <> '' and LEN(LTRIM(RTRIM(@SEARCH_PART_NO))) > 0
		BEGIN
			SET @FG_REPORT = @FG_REPORT + ' AND  PART_NUMBER like  ''' + '%' + @search_Part_no + '%' + ''''
		END
		--PRINT ('@FG_REPORT ----'+@FG_REPORT)

		SET @FGTRANSIT_STRING = N'INSERT INTO #FG_RESULTS
		SELECT 
			LT.PROCESSSTEP_ID,L_VENDOR_NAME AS FG_VENDOR,
			LOT_NUMBER,FAB_LOT_NUMBER,LOT_CATEGORY,SHIP_QTY,SHIP_DATE,
			STATUS,
			LOT_DATE_CODE,NULL AS EXPIRY_DATE,DATEDIFF( DD,SHIP_DATE,CURRENT_TIMESTAMP ) AS lot_AGE,
			NULL as EXPECTED_DATE_OUT ,PART_NUMBER,
			(SELECT PARAMETER_VALUE	FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ''PIN'' AND PROCESSSTEP_ID = PS.PROCESSSTEP_ID ) AS PIN_COUNT,
			 P.PART_STATUS,
			(SELECT PARAMETER_VALUE	FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ''PACKAGE ID'' AND PROCESSSTEP_ID = APT.ASSEMBLY_PROCESSSTEP_ID ) AS PACKAGE_TYPE,
			NULL AS ASSEMBLY_VENDOR,APT.ASSEMBLY_PROCESSSTEP_ID
		FROM 
			SHIPPED_LOT_history SL,LOT_TABLE LT, PRODUCT P, PROCESS_STEP PS,#ASSEMBLY_PROCESSSTEP_TABLE APT,LOCATION L
		WHERE 
			SL.LOT_ID = LT.LOT_ID
			AND LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
			AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID 
			AND PS.PROCESSSTEP_ID = APT.FGPROCESSSTEP_ID
			AND LT.LOCATION_ID = L.LOCATION_ID
			AND SL.STATUS = ''TRANSIT''
			AND PS_TYPE = ''FG'' 
			AND ( SL.TIMESTAMP BETWEEN '''+ CONVERT(VARCHAR,@START_DATE)+''''+' AND '''+ CONVERT(VARCHAR,@END_DATE)+''') ' 
	
		IF @PROCESSSTEP_IDS <> '0'
		BEGIN
			SET @FGTRANSIT_STRING = @FGTRANSIT_STRING + ' AND LT.PROCESSSTEP_ID IN ( SELECT STEP_ID FROM #STEP_IDS_TABLE ) '
		END
		IF @VENDOR <> '0'
		BEGIN
			SET @FGTRANSIT_STRING = @FGTRANSIT_STRING + ' AND L.L_VENDOR_NAME = '''+ @VENDOR +''''
		END
		IF @LOT_CATEGORY <> '0'
		BEGIN
			SET @FGTRANSIT_STRING = @FGTRANSIT_STRING + ' AND LOT_CATEGORY = '''+ @LOT_CATEGORY+''''
		END
		/*IF @STATUS = '0'
		BEGIN
			SET @FGTRANSIT_STRING = @FGTRANSIT_STRING + ' AND ( LOT_CURRENT_STATUS = ''RUNNING'' OR LOT_CURRENT_STATUS = ''HOLD'' ) '
		END
		ELSE
		BEGIN
			SET @FGTRANSIT_STRING = @FGTRANSIT_STRING + ' AND LOT_CURRENT_STATUS = '''+ @STATUS+''''
		END*/
		IF @SEARCH_PART_NO <> '' and LEN(LTRIM(RTRIM(@SEARCH_PART_NO))) > 0
		BEGIN
			SET @FGTRANSIT_STRING = @FGTRANSIT_STRING + ' AND  PART_NUMBER like  ''' + '%' + @search_Part_no + '%' + ''''
		END

		exec (@FG_REPORT+@FGTRANSIT_STRING)

		DECLARE @FG_cursor CURSOR
		DECLARE @ASSM_cursor CURSOR

		DECLARE @FG_STEP_ID INT
		DECLARE @ASSM_STEP_ID INT
		DECLARE @ASSM_VENDOR VARCHAR(50)
		DECLARE @ASSM_VENDORS VARCHAR(200)
		DECLARE @LOT_NUMBER VARCHAR(50)
		DECLARE @FAB_LOT_NUMBER VARCHAR(50)

		SET @FG_cursor = CURSOR FOR SELECT PROCESSSTEP_ID,FAB_LOT_NUMBER,LOT_NUMBER,ASSEMBLY_PROCESSSTEP_ID FROM #FG_RESULTS 
		OPEN @FG_cursor
		FETCH NEXT FROM @FG_cursor INTO @FG_STEP_ID,@FAB_LOT_NUMBER,@LOT_NUMBER,@ASSM_STEP_ID
		WHILE @@FETCH_STATUS = 0
		BEGIN	
			SET @ASSM_cursor = CURSOR FOR SELECT distinct L_VENDOR_NAME 
						FROM 
							LOT_TABLE LT1,LOCATION L1 
						WHERE 
							LT1.LOCATION_ID = L1.LOCATION_ID 
							AND LT1.PROCESSSTEP_ID = @ASSM_STEP_ID
							AND LT1.fab_LOT_NUMBER = @FAB_LOT_NUMBER 
			OPEN @ASSM_cursor 
			FETCH NEXT FROM @ASSM_cursor INTO @ASSM_VENDOR
			WHILE @@FETCH_STATUS = 0
			BEGIN	
				IF ( @ASSM_VENDORS IS NULL )
				BEGIN
					SET @ASSM_VENDORS = @ASSM_VENDOR  
				END
				ELSE
				BEGIN
					SET @ASSM_VENDORS = @ASSM_VENDORS +','+ @ASSM_VENDOR  
				END
			FETCH NEXT FROM @ASSM_cursor INTO @ASSM_VENDOR
			END
			CLOSE @ASSM_cursor 
			DEALLOCATE @ASSM_cursor 

			UPDATE #FG_RESULTS SET ASSEMBLY_VENDOR = @ASSM_VENDORS WHERE PROCESSSTEP_ID = @FG_STEP_ID AND FAB_LOT_NUMBER = @FAB_LOT_NUMBER

				SET @ASSM_VENDORS = NULL
				SET @ASSM_VENDOR  = NULL

			FETCH NEXT FROM @FG_cursor INTO @FG_STEP_ID,@FAB_LOT_NUMBER,@LOT_NUMBER,@ASSM_STEP_ID
		END

		CLOSE @FG_cursor
		DEALLOCATE @FG_cursor

		SELECT 	PROCESSSTEP_ID,FG_VENDOR, LOT_NUMBER, FAB_LOT_NUMBER ,
			LOT_CATEGORY , QTY_IN ,DATE_IN ,LOT_CURRENT_STATUS ,
			LOT_DATE_CODE ,EXPIRY_DATE ,lot_AGE ,EXPECTED_DATE_OUT , 
			PART_NUMBER ,PIN_COUNT ,PART_STATUS ,PACKAGE_TYPE ,
			ASSEMBLY_VENDOR ,ASSEMBLY_PROCESSSTEP_ID 
		from 
			#FG_RESULTS 
	END
	ELSE
	BEGIN
	
		DECLARE @ASOFDATE_SQLSTRING VARCHAR(4000)
		SET @ASOFDATE_SQLSTRING = N'
			INSERT INTO #WIP_RESULTS (PROCESSSTEP_ID,LOCATION,
						  LOT_NUMBER,FAB_LOT_NUMBER,LOT_CATEGORY,WAFER_QTY_IN,QTY_IN,DATE_IN,
						  STAGE_NAME,STAGE_POSITION,LOT_CURRENT_STATUS,EXPECTED_DATE_OUT,OUTLOOK_DATE,
						  GROSS_DIE,WAFER_ID,PART_NUMBER,BASE_PRODUCT,PS_TYPE_NAME,PS_TYPE_ID,PACKAGE_TYPE)
			SELECT 
				LTH.PROCESSSTEP_ID,L.L_VENDOR_NAME,
				LTH.LOT_NUMBER, LTH.FAB_LOT_NUMBER, isnull(LTH.LOT_CATEGORY, ''PROD'' ), LTH.WAFER_QTY_IN, LTH.QTY_IN,LTH.DATE_IN,
				LTH.STAGE_NAME, LTH.STAGE_POSITION, LOT_CURRENT_STATUS, NULL AS EXPECTED_DATE_OUT,NULL AS OUTLOOK_DATE,
				convert(int,cast((SELECT PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ''Gross Die/Wafer'' AND PROCESSSTEP_ID = LTH.PROCESSSTEP_ID) as float)) AS GROSS_DIE_WAFER,
				NULL AS WAFER_ID,P.PART_NUMBER,
				(SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
					CASE 
						WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
						WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
					END 
				) AS BASE_PRODUCT,
				PS_TYPE,
				(CASE
					WHEN PS_TYPE = ''FAB'' THEN 1
					WHEN PS_TYPE = ''WAFER BANK'' THEN 2
					WHEN PS_TYPE = ''Sort'' THEN 3
					WHEN PS_TYPE = ''DIE BANK'' THEN 3
					WHEN PS_TYPE = ''ASSEMBLY'' THEN 5
					WHEN PS_TYPE = ''Pre Test'' THEN 6
					WHEN PS_TYPE = ''FT'' THEN 7
					WHEN PS_TYPE = ''EOL'' THEN 8
					WHEN PS_TYPE = ''Tape & Reel'' THEN 9
					WHEN PS_TYPE = ''FG'' THEN 10
				   END 
				) AS PS_TYPE_ID,
				NULL AS PARAMETER_VALUE --,lth.timestamp
				 
			FROM 
				LOT_TABLE_HISTORY LTH,PRODUCT P,PROCESS_STEP PS ,LOCATION L
			WHERE
				LTH.PROCESSSTEP_ID = P.PROCESSSTEP_ID
				AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
				AND LTh.TimeStamp = ( SELECT MAX(LTH1.Timestamp) FROM LOT_TABLE_HISTORY LTH1 WHERE LTH1.TIMESTAMP BETWEEN '''+ CONVERT(VARCHAR,@START_DATE)+''''+' AND '''+ CONVERT(VARCHAR,@END_DATE)+'''
				AND LTH.Lot_ID = Lth1.Lot_ID )
				AND LTH.LOCATION_ID = L.LOCATION_ID
				AND P.P_TYPE IS NULL '

			--SET @ASOFDATE_SQLSTRING = @ASOFDATE_SQLSTRING + ' AND LTH.TIMESTAMP BETWEEN '''+ CONVERT(VARCHAR,@START_DATE)+''''+' AND '''+ CONVERT(VARCHAR,@END_DATE)+''''  
		
			IF @PROCESS_TYPE = '0'
			BEGIN
				SET @ASOFDATE_SQLSTRING = @ASOFDATE_SQLSTRING + ' AND 1=1 '
			END
			ELSE IF @PROCESS_TYPE = 'WIP'
			BEGIN
				SET @ASOFDATE_SQLSTRING = @ASOFDATE_SQLSTRING + ' AND ( PS_TYPE = ''FAB'' OR  PS_TYPE = ''SORT'' OR PS_TYPE = ''ASSEMBLY'' OR PS_TYPE = ''FPO'' OR PS_TYPE = ''FT'' ) '
			END
			ELSE IF @PROCESS_TYPE = 'INVENTORY'
			BEGIN
				SET @ASOFDATE_SQLSTRING = @ASOFDATE_SQLSTRING + ' AND ( PS_TYPE = ''WAFER BANK'' OR PS_TYPE = ''DIE BANK'' OR PS_TYPE = ''PRE TEST'' OR PS_TYPE = ''EOL'' OR PS_TYPE = ''TAPE & REEL'' OR PS_TYPE = ''FG'' )  '
			END
			ELSE 
			BEGIN
				SET @ASOFDATE_SQLSTRING = @ASOFDATE_SQLSTRING + ' AND PS_TYPE = '''+ @PROCESS_TYPE+''''
			END
			
			IF @PROCESSSTEP_IDS <> '0'
			BEGIN
				SET @ASOFDATE_SQLSTRING = @ASOFDATE_SQLSTRING + ' AND LTH.PROCESSSTEP_ID IN ( SELECT STEP_ID FROM #STEP_IDS_TABLE ) '
			END
			IF @VENDOR <> '0'
			BEGIN
				SET @ASOFDATE_SQLSTRING = @ASOFDATE_SQLSTRING + ' AND L_VENDOR_NAME = '''+ @VENDOR+''''
			END
			IF @LOT_CATEGORY <> '0'
			BEGIN
				SET @ASOFDATE_SQLSTRING = @ASOFDATE_SQLSTRING + ' AND LOT_CATEGORY = '''+ @LOT_CATEGORY+''''
			END
			IF @STATUS <> '0'
			BEGIN
				SET @ASOFDATE_SQLSTRING = @ASOFDATE_SQLSTRING + ' AND LOT_CURRENT_STATUS = '''+ @STATUS+''''
			END	
			ELSE
			BEGIN
				SET @ASOFDATE_SQLSTRING = @ASOFDATE_SQLSTRING + ' AND ( LOT_CURRENT_STATUS = ''RUNNING'' OR LOT_CURRENT_STATUS = ''HOLD'' ) '
			END
			IF @SEARCH_PART_NO <> '' and LEN(LTRIM(RTRIM(@SEARCH_PART_NO))) > 0
			BEGIN
				SET @ASOFDATE_SQLSTRING = @ASOFDATE_SQLSTRING + ' AND  PART_NUMBER like  ''' + '%' + @search_Part_no + '%' + ''''
			END
	
			--PRINT '@ASOFDATE_SQLSTRING :- '+@ASOFDATE_SQLSTRING
			EXEC (@ASOFDATE_SQLSTRING)

		IF @REPORT_TYPE = 'ALL'
		BEGIN
			SET @TRANSIT_STRING = N'
			INSERT INTO #WIP_RESULTS 
				( DATE_IN,QTY_IN,WAFER_QTY_IN,LOT_CURRENT_STATUS,WAFER_ID,PROCESSSTEP_ID, LOCATION, LOT_NUMBER, FAB_LOT_NUMBER,
				LOT_CATEGORY,PART_NUMBER,BASE_PRODUCT,BASE_PRODUCT_ID,PS_TYPE_NAME,PS_TYPE_ID, STAGE_NAME, PACKAGE_TYPE) 
		
			SELECT 
				ship_date,
				(CASE WHEN (PS_TYPE != ''FAB'' AND PS_TYPE != ''FAB OPTION'' AND PS_TYPE != ''WAFER BANK'' AND PS_TYPE != ''SORT'' ) THEN SHIP_QTY ELSE NULL END ), 
				(CASE WHEN (PS_TYPE = ''FAB'' OR PS_TYPE = ''FAB OPTION'' OR PS_TYPE = ''WAFER BANK'' OR PS_TYPE = ''SORT'' ) THEN SHIP_QTY ELSE NULL END ),
				status,WAFER_ID,LT.processstep_id,(SELECT L_Vendor_Name FROM Location WHERE Location_ID = Lt.Location_Id ),
				lot_number,fab_lot_number,lt.lot_category,part_number,
				( SELECT Part_Number FROM PRODUCT WHERE Product_Id = ( SELECT P_Parent_Product_Id FROM  Product WHERE ProcessStep_Id = P.ProcessStep_ID )),
				P_Parent_Product_Id,ps_type,
				(CASE
					WHEN PS_TYPE = ''FAB'' THEN 1
					WHEN PS_TYPE = ''WAFER BANK'' THEN 2
					WHEN PS_TYPE = ''Sort'' THEN 3
					WHEN PS_TYPE = ''DIE BANK'' THEN 3
					WHEN PS_TYPE = ''ASSEMBLY'' THEN 5
					WHEN PS_TYPE = ''Pre Test'' THEN 6
					WHEN PS_TYPE = ''FT'' THEN 7
					WHEN PS_TYPE = ''EOL'' THEN 8
					WHEN PS_TYPE = ''Tape & Reel'' THEN 9
					WHEN PS_TYPE = ''FG'' THEN 10
					ELSE NULL 
				END),''Transit'',
				( case 
					when ps_type = ''assembly'' then ( SELECT PARAMETER_VALUE FROM PROCESS_PARAMETER PP  WHERE PARAMETER_KEY = ''PACKAGE ID'' AND PP.PROCESSSTEP_ID = lt.PROCESSSTEP_ID )
					when ( ps_type = ''ft'' or ps_type = ''Pre Test'' or ps_type = ''EOL'' or ps_type = ''tape & reel'' or ps_type = ''fg'' ) then 
						( SELECT PARAMETER_VALUE FROM PROCESS_PARAMETER PP,#ASSEMBLY_PROCESSSTEP_TABLE APT  WHERE PARAMETER_KEY = ''PACKAGE ID'' AND PP.PROCESSSTEP_ID = APT.ASSEMBLY_PROCESSSTEP_ID AND FGPROCESSSTEP_ID = lt.PROCESSSTEP_ID )
					else null
				end )
			FROM 
				SHIPPED_LOT_HISTORY SL,LOT_TABLE LT,PRODUCT P,PROCESS_STEP PS 
			WHERE 
				SL.LOT_ID = LT.LOT_ID
				AND LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
				AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID 
				AND SL.STATUS <> ''CLOSE'' 
				AND ( SL.TIMESTAMP BETWEEN '''+ CONVERT(VARCHAR,@START_DATE)+''''+' AND '''+ CONVERT(VARCHAR,@END_DATE)+''') ' 
			
			IF @PROCESS_TYPE <> '0'
			BEGIN
				SET @TRANSIT_STRING = @TRANSIT_STRING + ' AND PS.PS_TYPE = '''+ @PROCESS_TYPE+''''
			END
			IF @PROCESSSTEP_IDS <> '0'
			BEGIN
				SET @TRANSIT_STRING = @TRANSIT_STRING + ' AND lt.PROCESSSTEP_ID IN ( SELECT STEP_ID FROM #STEP_IDS_TABLE ) '
			END
			IF @VENDOR <> '0'
			BEGIN
				SET @TRANSIT_STRING = @TRANSIT_STRING + ' AND LT.LOCATION_ID IN ( SELECT LOCATION_ID FROM LOCATION WHERE L_VENDOR_NAME = '''+ @VENDOR +''')'
			END
			IF @LOT_CATEGORY <> '0'
			BEGIN
				SET @TRANSIT_STRING = @TRANSIT_STRING + ' AND LOT_CATEGORY = '''+ @LOT_CATEGORY+''''
			END
			/*IF @STATUS <> '0'
			BEGIN
				SET @TRANSIT_STRING = @TRANSIT_STRING + ' AND LOT_CURRENT_STATUS = '''+ @STATUS+''''
			END*/
			IF @SEARCH_PART_NO <> '' and LEN(LTRIM(RTRIM(@SEARCH_PART_NO))) > 0
			BEGIN
				SET @TRANSIT_STRING = @TRANSIT_STRING + ' AND  PART_NUMBER like  ''' + '%' + @search_Part_no + '%' + ''''
			END

			EXEC (@TRANSIT_STRING)
		END
	
		SELECT  
			PROCESSSTEP_ID, LOCATION, LOT_NUMBER, FAB_LOT_NUMBER,
			LOT_CATEGORY, WAFER_QTY_IN, QTY_IN, DATE_IN, STAGE_NAME, 
			STAGE_POSITION, (CASE 
						WHEN LOT_CURRENT_STATUS = 'Running' THEN 'ACTIVE'
						ELSE UPPER(LOT_CURRENT_STATUS)
					END) AS LOT_CURRENT_STATUS, 
			EXPECTED_DATE_OUT, DATEDIFF( DD,DATE_IN,@ASOFDATE ) as AGE,
			OUTLOOK_DATE, GROSS_DIE ,WAFER_ID,PART_NUMBER, BASE_PRODUCT, 
			BASE_PRODUCT_ID, PS_TYPE_NAME,PS_TYPE_ID,THINKNESS,GRIND,
			SL_FLOW,SL_TEST_PROGRAM,SL_HOLD_DATE,SL_TESTER,MASK,LABLE_NAME,PACKAGE_TYPE
		FROM 
			#WIP_RESULTS ORDER BY BASE_PRODUCT,PS_TYPE_ID,PART_NUMBER,STAGE_POSITION,PACKAGE_TYPE
	END	

END
